﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;


namespace MyLibellus
{
    class BookRelacions
    {

        public static void afegirBookSubject(Subject s, Book b)
        {
            string insert="insert into bookSubject values('"+b.Isbn+"', "+s.Id+")";
            SqlConnection con=DBAccess.getConnection();
             con.Open();
            SqlCommand sc = new SqlCommand(insert, con);
            sc.ExecuteNonQuery();
            con.Close();
        }

        public static void afegirBookAuthor(Author a, Book b)
        {
            string insert = "insert into bookAuthor values('" + b.Isbn + "', " + a.Id + ")";
            SqlConnection con = DBAccess.getConnection();
            con.Open();
            SqlCommand s = new SqlCommand(insert, con);

            s.ExecuteNonQuery();
            con.Close();
        }
        public static void afegirListBook(Book b)        
        {
            foreach (Author a in b.LA)
            {
                afegirBookAuthor(a, b);
            }
            foreach (Subject s in b.LS)
            {
                afegirBookSubject(s, b);
            }
        }

        public static void EliminarBookSubject(Subject s, Book b)
        {
            string del = "delete from bookSubject where subject_id='" + s.Id + "' and isbn= '"+b.Isbn+"'";
            SqlConnection con = DBAccess.getConnection();
            con.Open();
            SqlCommand sc = new SqlCommand(del, con);
            sc.ExecuteNonQuery();
            con.Close();
        }

        public static void elminarListBook(Book b)
        {
            foreach (Subject s in b.LS)
            {
                EliminarBookSubject(s, b);
            }
            foreach (Author a in b.LA)
            {
                EliminarBookAuthor(a, b);
            }
        }

        public static void EliminarBookAuthor(Author a, Book b)
        {
            string del = "delete from bookAuthor where id_author='" + a.Id + "' and isbn= '"+b.Isbn+"'";
            SqlConnection con = DBAccess.getConnection();
            con.Open();
            SqlCommand s = new SqlCommand(del, con);
            s.ExecuteNonQuery();
            con.Close();
        }

        public static List<Subject> getSubjects(Book b)
        {
            List<Subject> ls = new List<Subject>();
            string select = "select subject_id, subject from bookSubject, subject where subject_id= id and isbn= '" + b.Isbn + "'";
            SqlConnection con = DBAccess.getConnection();
            con.Open();
            SqlCommand sc = new SqlCommand(select, con);
            SqlDataReader dReader=  sc.ExecuteReader();
            while (dReader.Read())
            {
                ls.Add( new Subject(int.Parse(dReader[0].ToString()), dReader[1].ToString()));               
            }
            con.Close();
            return ls;

        }
        public static List<Author> getAuthors(Book b)
        {
            List<Author> la = new List<Author>();
            string select = "select  author.id_author, full_name from author, bookAuthor where author.id_author= bookAuthor.id_author and bookAuthor.isbn= '" + b.Isbn + "'";
            SqlConnection con = DBAccess.getConnection();
            con.Open();
            SqlCommand sc = new SqlCommand(select, con);
            SqlDataReader dReader = sc.ExecuteReader();
            while (dReader.Read())
            {
                la.Add(new Author(dReader[1].ToString(), int.Parse(dReader[0].ToString())));
            }

            return la;

        }
    }
}
